package com.panghl.service;


import cn.afterturn.easypoi.csv.CsvExportUtil;
import cn.afterturn.easypoi.csv.entity.CsvExportParams;
import cn.afterturn.easypoi.entity.ImageEntity;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.word.WordExportUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.opencsv.CSVWriter;
import com.panghl.mapper.ResourceMapper;
import com.panghl.mapper.UserMapper;
import com.panghl.pojo.User;
import com.panghl.utils.EntityUtils;
import com.panghl.utils.ExcelExportEngine;
import com.zaxxer.hikari.HikariDataSource;
import net.sf.jasperreports.engine.*;
import net.sf.jasperreports.engine.data.JRBeanCollectionDataSource;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ResourceUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.imageio.ImageIO;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.swing.text.html.parser.Entity;
import java.awt.image.BufferedImage;
import java.io.*;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
public class UserService {
    private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    private static File rootPath; //SpringBoot项目获取根目录的方式

    static {
        try {
            rootPath = new File(ResourceUtils.getURL("classpath:").getPath());
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }

    @Resource
    private UserMapper userMapper;
    @Resource
    private ResourceMapper resourceMapper;

    public List<User> findAll() {
        return userMapper.selectList(null);
    }

    public List<User> findPage(Integer page, Integer pageSize) {
        Page<User> pageObj = new Page(page, pageSize);
        return userMapper.selectPage(pageObj, null).getRecords();
    }

    public User findById(Long id) {
        //根据id查询用户，并且用户中附带公共用品数据
        User user = userMapper.selectById(id);
        //在查询办公用品数据
        List<com.panghl.pojo.Resource> resources = resourceMapper.selectList(new QueryWrapper<com.panghl.pojo.Resource>().lambda().eq(com.panghl.pojo.Resource::getUserId, id));
        user.setResourceList(resources);
        return user;
    }

/*
    public void downLoadXlsByJxl(HttpServletResponse response) throws IOException, WriteException {
        OutputStream outputStream = response.getOutputStream();
        //1、创建可写入的Excel工作薄
        WritableWorkbook workbook = Workbook.createWorkbook(outputStream);
        //2、创建工作表
        WritableSheet sheet = workbook.createSheet("一个JXL入门", 0);
        // 处理标题
        String[] titles = new String[]{"编号", "姓名", "手机号", "入职日期", "现住址"};
        Label label = null;
        for (int i = 0; i < titles.length; i++) {
            //3、创建单元格 添加文本类单元格
            label = new Label(i, 0, titles[i]);
            sheet.addCell(label);
        }

        //获取所有用户数据
        List<User> userList = userMapper.selectList(null);
        int indexRow = 1;
        for (int i = 0; i < userList.size(); i++) {
            User user = userList.get(i);

            label = new Label(0, indexRow, user.getId().toString());
            sheet.addCell(label);
            label = new Label(1, indexRow, user.getUserName().toString());
            sheet.addCell(label);
            label = new Label(2, indexRow, user.getPhone().toString());
            sheet.addCell(label);
            label = new Label(3, indexRow, sdf.format(user.getHireDate()).toString());
            sheet.addCell(label);
            label = new Label(4, indexRow, user.getAddress().toString());
            sheet.addCell(label);
            indexRow++;
        }
        //导出的文件名称
        String filename = "一个JXL入门.xls";
        //设置文件的打开方式和mime类型
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.ms-excel");
        //4、写入到文件
        workbook.write();// 写入数据
        //5、释放资源：
        workbook.close();// 关闭文件
        outputStream.close();
    }
*/


    public void uploadExcel(MultipartFile file) throws ParseException, IOException {

        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());//根据上传的输入流创建workbook
        Sheet sheet = workbook.getSheetAt(0); //获取工作薄中的第一个工作表
        int lastRowIndex = sheet.getLastRowNum(); //获取这个sheet中最后一行数据，为了循环遍历

        //以下三个为了节省栈内存，所以提到循环的外面
        User user = null;
        Row row = null;
        Cell cell = null;

        //开始循环每行，获取每行的单元格中的值，放入到user属性中
        for (int i = 1; i <= lastRowIndex; i++) {
            row = sheet.getRow(i);
            user = new User();
            // 因为第一个列单元格中是字符串，可以直接使用getStringCellValue方法
            String userName = row.getCell(0).getStringCellValue(); //用户名
            user.setUserName(userName);
            String phone = null; //手机号
            try {
                phone = row.getCell(1).getStringCellValue();
            } catch (IllegalStateException e) {
                phone = row.getCell(1).getNumericCellValue() + "";
            }
            user.setPhone(phone);
            String province = row.getCell(2).getStringCellValue(); //省份
            user.setProvince(province);
            String city = row.getCell(3).getStringCellValue(); //城市
            user.setCity(city);
            //            因为在填写excel中的数据时就可以约定这个列只能填写数值，所以可以直接用getNumericCellValue方法
            Integer salary = ((Double) row.getCell(4).getNumericCellValue()).intValue(); //工资
            user.setSalary(salary);
            String hireDateStr = row.getCell(5).getStringCellValue(); //入职日期
            Date hireDate = sdf.parse(hireDateStr);
            user.setHireDate(hireDate);
            String birthdayStr = row.getCell(6).getStringCellValue(); //出生日期
            Date birthday = sdf.parse(birthdayStr);
            user.setBirthday(birthday);

            String address = row.getCell(7).getStringCellValue(); //现住地址
            user.setAddress(address);
            userMapper.insert(user);
        }
    }

    public void downLoadXlsxByPoi(HttpServletResponse response) throws IOException {
        //        创建一个空的工作薄
        Workbook workbook = new XSSFWorkbook();

        //        在工作薄中创建一个工作表
        Sheet sheet = workbook.createSheet("测试");
        //设置列宽
        sheet.setColumnWidth(0, 200 * 256); // 1代表的是一个标准字母宽度的256分之一
//列宽
        sheet.setColumnWidth(0, 5 * 256);
        sheet.setColumnWidth(1, 6 * 256);
        sheet.setColumnWidth(2, 7 * 256);
        sheet.setColumnWidth(3, 8 * 256);
        sheet.setColumnWidth(4, 9 * 256);
        //1. 边框线
        CellStyle bigTitleRowCellStyle = workbook.createCellStyle();
        bigTitleRowCellStyle.setBorderBottom(BorderStyle.THIN); // THIN 细线
        bigTitleRowCellStyle.setBorderLeft(BorderStyle.THIN);
        bigTitleRowCellStyle.setBorderRight(BorderStyle.THIN);
        bigTitleRowCellStyle.setBorderTop(BorderStyle.THIN);
        // 对齐方式： 水平对齐，垂直对齐
        bigTitleRowCellStyle.setAlignment(HorizontalAlignment.CENTER);
        bigTitleRowCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //创建字体
        Font font = workbook.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 18);
        //把字体放入到样式中
        bigTitleRowCellStyle.setFont(font);
        Row bigTitleRow = sheet.createRow(0);
        //2. 行高
        bigTitleRow.setHeightInPoints(42);
        for (int i = 0; i < 5; i++) {
            Cell cell = bigTitleRow.createCell(i);
            cell.setCellStyle(bigTitleRowCellStyle);
        }
        //3. 合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
        //向单元格中放入一句话
        sheet.getRow(0).getCell(0).setCellValue("用户表信息");


        //小标题的样式
        CellStyle littleTitleRowCellStyle = workbook.createCellStyle();
        //样式的克隆
        littleTitleRowCellStyle.cloneStyleFrom(bigTitleRowCellStyle);
        // 对齐方式： 水平对齐，垂直对齐
        littleTitleRowCellStyle.setAlignment(HorizontalAlignment.CENTER);
        littleTitleRowCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //创建字体
        Font littleFont = workbook.createFont();
        littleFont.setFontName("宋体");
        littleFont.setFontHeightInPoints((short) 12);
        littleFont.setBold(true);
        //把字体放入到样式中
        littleTitleRowCellStyle.setFont(littleFont);


        //内容的样式
        CellStyle contentRowCellStyle = workbook.createCellStyle();
        //样式的克隆
        contentRowCellStyle.cloneStyleFrom(littleTitleRowCellStyle);
        contentRowCellStyle.setAlignment(HorizontalAlignment.CENTER);
        //创建字体
        Font contentFont = workbook.createFont();
        contentFont.setFontName("宋体");
        contentFont.setFontHeightInPoints((short) 11);
        contentFont.setBold(false);
        //把字体放入到样式中
        contentRowCellStyle.setFont(contentFont);

        Row titleRow = sheet.createRow(1);
        titleRow.setHeightInPoints(30);
        String[] titles = new String[]{"编号", "姓名", "手机号", "入职日期", "现住址"};
        for (int i = 0; i < 5; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(littleTitleRowCellStyle);
        }


        //        处理内容
        List<User> userList = this.findAll();
        int rowIndex = 2;
        Row row = null;
        Cell cell = null;
        for (User user : userList) {
            row = sheet.createRow(rowIndex);
            cell = row.createCell(0);
            cell.setCellValue(user.getId());
            cell.setCellStyle(contentRowCellStyle);

            cell = row.createCell(1);
            cell.setCellValue(user.getUserName());
            cell.setCellStyle(contentRowCellStyle);

            cell = row.createCell(2);
            cell.setCellValue(user.getPhone());
            cell.setCellStyle(contentRowCellStyle);

            cell = row.createCell(3);
            cell.setCellValue(sdf.format(user.getHireDate()));
            cell.setCellStyle(contentRowCellStyle);

            cell = row.createCell(4);
            cell.setCellValue(user.getAddress());
            cell.setCellStyle(contentRowCellStyle);
            rowIndex++;
        }

        //            导出的文件名称
        String filename = "员工数据.xlsx";
        //            设置文件的打开方式和mime类型
        ServletOutputStream outputStream = response.getOutputStream();
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        workbook.write(outputStream);
    }


    public void downLoadXlsxWithTempalte(HttpServletResponse response) throws IOException, InvalidFormatException {
        //1. 获取模板的路径
        File rootPath = new File(ResourceUtils.getURL("classpath:").getPath()); //SpringBoot项目获取根目录的方式
        File templatePath = new File(rootPath.getAbsolutePath(), "/excel_template/userList.xlsx");
        //读取模板文件产生workbook对象,这个workbook是一个有内容的工作薄
        Workbook workbook = new XSSFWorkbook(templatePath);
        //读取工作薄的第一个工作表，向工作表中放数据
        Sheet sheet = workbook.getSheetAt(0);
        //获取第二个的sheet中那个单元格中的单元格样式
        CellStyle cellStyle = workbook.getSheetAt(1).getRow(0).getCell(0).getCellStyle();
        //2.查询所有的用户数据
        List<User> userList = this.findAll();
        //3.放入到模板中
        int rowIndex = 2;
        Row row = null;
        Cell cell = null;
        for (User user : userList) {
            row = sheet.createRow(rowIndex);
            row.setHeightInPoints(15); //设置行高

            cell = row.createCell(0);
            cell.setCellValue(user.getId());
            cell.setCellStyle(cellStyle); //设置单元格样式

            cell = row.createCell(1);
            cell.setCellValue(user.getUserName());
            cell.setCellStyle(cellStyle);

            cell = row.createCell(2);
            cell.setCellValue(user.getPhone());
            cell.setCellStyle(cellStyle);

            cell = row.createCell(3);
            cell.setCellValue(sdf.format(user.getHireDate()));
            cell.setCellStyle(cellStyle);

            cell = row.createCell(4);
            cell.setCellValue(user.getAddress());
            cell.setCellStyle(cellStyle);

            rowIndex++;
        }
        //把第二个sheet删除
        workbook.removeSheetAt(1);

        //4.导出文件
        String filename = "用户列表数据.xlsx";
        ServletOutputStream outputStream = response.getOutputStream();
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        workbook.write(outputStream);
        outputStream.close();
    }

    /**
     * 导出用户的详细信息
     *
     * @param id
     * @param response
     */
    public void downloadUserInfoByTemplate(Long id, HttpServletResponse response) throws Exception {
        //1.读取模块
        File templatePath = new File(rootPath.getAbsolutePath(), "/excel_template/userInfo.xlsx");
        //读取模板文件产生workbook对象,这个workbook是一个有内容的工作薄
        Workbook workbook = new XSSFWorkbook(templatePath);
        //读取工作薄的第一个工作表，向工作表中放数据
        Sheet sheet = workbook.getSheetAt(0);
        //2.获取用户数据
        User user = userMapper.selectById(id);
        //3. 把用户数据放入到模板中
        //        用户名   第2行第2列
        sheet.getRow(1).getCell(1).setCellValue(user.getUserName());
//        手机号   第3行第2列
        sheet.getRow(2).getCell(1).setCellValue(user.getPhone());
//        生日     第4行第2列  日期转成字符串
        sheet.getRow(3).getCell(1).setCellValue
                (sdf.format(user.getBirthday()));
//        工资 第5行第2列
        sheet.getRow(4).getCell(1).setCellValue(user.getSalary());
//        工资 第6行第2列
        sheet.getRow(5).getCell(1).setCellValue
                (sdf.format(user.getHireDate()));
//        省份     第7行第2列
        sheet.getRow(6).getCell(1).setCellValue(user.getProvince());
//        现住址   第8行第2列
        sheet.getRow(7).getCell(1).setCellValue(user.getAddress());
//        司龄     第6行第4列暂时先不考虑  =CONCATENATE(DATEDIF(B6,TODAY(),"y"),"年",DATEDIF(B6,TODAY(),"YM"),"个月")
//        sheet.getRow(5).getCell(3).setCellFormula("CONCATENATE(DATEDIF(B6,TODAY(),\"y\"),\"年\",DATEDIF(B6,TODAY(),\"YM\"),\"个月\")");

//        城市     第7行第4列
        sheet.getRow(6).getCell(3).setCellValue(user.getCity());

        //照片
        setImage(user.getPhoto(), workbook);

        //4.导出
        //            导出的文件名称
        String filename = "用户详细信息数据.xlsx";
        //设置文件的打开方式和mime类型
        ServletOutputStream outputStream = response.getOutputStream();
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        workbook.write(outputStream);
        outputStream.close();
    }

    private void setImage(String photo, Workbook workbook) throws IOException {
        Sheet sheet = workbook.getSheetAt(0);
        //      照片的位置
        //开始处理照片
        //先创建一个字节输出流
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        //读取图片   放入一个带有缓存区的图片类中
        String extName = photo.substring(photo.lastIndexOf(".") + 1).toUpperCase();
        BufferedImage bufferedImage = ImageIO.read(new File(rootPath + photo));
        //把图片写入到了字节输出流中
        ImageIO.write(bufferedImage, extName, byteArrayOutputStream);
        // 创建一个绘图控制类，负责画图
        Drawing patriarch = sheet.createDrawingPatriarch();
// 指定把图片放到哪个位置
        //偏移的单位：是一个英式公制的单位  1厘米=360000
        ClientAnchor anchor = new XSSFClientAnchor(360000, 0, 0, 0, 2, 1, 4, 5);

        int format = 0;
        switch (extName) {
            case "JPG":
                format = XSSFWorkbook.PICTURE_TYPE_JPEG;
        }

// 开始把图片写入到sheet指定的位置
        patriarch.createPicture(anchor, workbook.addPicture(
                byteArrayOutputStream.toByteArray(), format));
        //处理照片结束

    }

    public void downloadUserInfoByTemplate2(Long id, HttpServletResponse response) throws Exception {
        //1.读取模块
        File templatePath = new File(rootPath.getAbsolutePath(), "/excel_template/userInfo2.xlsx");
        //读取模板文件产生workbook对象,这个workbook是一个有内容的工作薄
        Workbook workbook = new XSSFWorkbook(templatePath);
        //2.获取用户数据
        User user = userMapper.selectById(id);
        //3. 把用户数据放入到模板中
        ExcelExportEngine.writeToExcel(rootPath, user.getPhoto(), user, workbook);
        //4.导出
        //            导出的文件名称
        String filename = "用户详细信息数据2.xlsx";
        //设置文件的打开方式和mime类型
        ServletOutputStream outputStream = response.getOutputStream();
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        workbook.write(outputStream);
        outputStream.close();
    }

    //百万数据的导出， 1. 肯定使用高版本的excel  2.使用sax方式解析Excel(XML)
    // 限制： 不能使用模板   不能使用太多的样式
    public void downLoadMillion(HttpServletResponse response) throws IOException {
        //执行使用的是sax方式解析
        Workbook workbook = new SXSSFWorkbook();

        //导出500w条数据 不能放到同一个sheet中 规定：每个sheet不能超过100w条
        int page = 1;
        int pageSize = 200000;
        int rowIndex = 1; //每一个工作页的行数
        int num = 0; //总数据量
        Row row = null;
        Cell cell = null;
        Sheet sheet = null;
        while (true) {  //不停地查询
            List<User> userList = this.findPage(page, pageSize);
            if (CollectionUtils.isEmpty(userList)) {  //如果查询不到就不再查询了
                break;
            }
            if (num % 1000000 == 0) {  //每100W个就重新创建新的sheet和标题
                rowIndex = 1;
                //        在工作薄中创建一个工作表
                sheet = workbook.createSheet("第" + ((num / 1000000) + 1) + "个工作表");
//        设置列宽
                sheet.setColumnWidth(0, 8 * 256);
                sheet.setColumnWidth(1, 12 * 256);
                sheet.setColumnWidth(2, 15 * 256);
                sheet.setColumnWidth(3, 15 * 256);
                sheet.setColumnWidth(4, 30 * 256);
                //            处理标题
                String[] titles = new String[]{"编号", "姓名", "手机号", "入职日期", "现住址"};
                //        创建标题行
                Row titleRow = sheet.createRow(0);

                for (int i = 0; i < titles.length; i++) {
                    cell = titleRow.createCell(i);
                    cell.setCellValue(titles[i]);
                }
            }

//        处理内容

            for (User user : userList) {
                row = sheet.createRow(rowIndex);
                cell = row.createCell(0);
                cell.setCellValue(user.getId());

                cell = row.createCell(1);
                cell.setCellValue(user.getUserName());

                cell = row.createCell(2);
                cell.setCellValue(user.getPhone());

                cell = row.createCell(3);
                cell.setCellValue(sdf.format(user.getHireDate()));

                cell = row.createCell(4);
                cell.setCellValue(user.getAddress());
                rowIndex++;
                num++;
            }
            page++;// 继续查询下一页
        }
//            导出的文件名称
        String filename = "百万数据.xlsx";
//            设置文件的打开方式和mime类型
        ServletOutputStream outputStream = response.getOutputStream();
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        workbook.write(outputStream);
    }

    //使用csv导出百万数据
    public void downLoadCSV(HttpServletResponse response) throws Exception {
        OutputStream outputStream = response.getOutputStream();
        String filename = "百万数据导出.csv";
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("text/csv");

        CSVWriter csvWriter = new CSVWriter(new OutputStreamWriter(outputStream, "utf-8"));
        //            先写头信息
        csvWriter.writeNext(new String[]{"编号", "姓名", "手机号", "入职日期", "现住址"});

        //            如果文件数据量非常大的时候，我们可以循环查询写入
        int page = 1;
        int pageSize = 200000;
        while (true) {  //不停地查询
            List<User> userList = this.findPage(page, pageSize);
            if (CollectionUtils.isEmpty(userList)) {  //如果查询不到就不再查询了
                break;
            }
            // 把查询到的数据转成数组放入到csv文件中
            for (User user : userList) {
                csvWriter.writeNext(new String[]{user.getId().toString(), user.getUserName(), user.getPhone(), sdf.format(user.getHireDate()), user.getAddress()});
            }
            csvWriter.flush();
            page++;
        }
        csvWriter.close();
    }



    /**
     * 下载word文档
     *
     * @param id
     * @param response
     */
    public void downloadContract(Long id, HttpServletResponse response) throws IOException {
        File templatePath = new File(rootPath.getAbsolutePath(), "/word_template/contract_template.docx");
        //1.读取到模板
        XWPFDocument word = new XWPFDocument(new FileInputStream(templatePath));
        //2.查询当前用户 User---> map
        User user = findById(id);
        Map<String, String> params = new HashMap<>();
        params.put("userName", user.getUserName());
        params.put("hireDate", sdf.format(user.getHireDate()));
        params.put("address", user.getAddress());

        //3.替换数据
        //处理正文
        List<XWPFParagraph> paragraphs = word.getParagraphs();
        for (XWPFParagraph paragraph : paragraphs) {
            // 获取片段
            List<XWPFRun> runs = paragraph.getRuns();
            for (XWPFRun run : runs) {
                String text = run.getText(0);
                for (String key : params.keySet()) {
                    if (text.contains(key)) {
                        run.setText(text.replaceAll(key, params.get(key)), 0);
                    }
                }
            }
        }
        //处理表格   名称 价值 是否需要归还  照片
        List<com.panghl.pojo.Resource> resourceList = user.getResourceList();
        XWPFTable table = word.getTables().get(0);
        XWPFTableRow titleRow = table.getRow(0);
        int rowIndex = 1;
        for (com.panghl.pojo.Resource resource : resourceList) {
            //添加行
            //table.addRow(titleRow);
            copyRow(table, titleRow, rowIndex);
            XWPFTableRow row = table.getRow(rowIndex);
            row.getCell(0).setText(resource.getName());
            row.getCell(1).setText(resource.getPrice().toString());
            row.getCell(2).setText(resource.getNeedReturn() ? "需要" : "不需要");

            File imagesFile = new File(rootPath, "/static" + resource.getPhoto());
            setCellImage(row.getCell(3), imagesFile);
            rowIndex++;
        }

        //4.导出word
        //            导出的文件名称
        String filename = "员工" + user.getUserName() + "合同信息.docx";
//            设置文件的打开方式和mime类型
        ServletOutputStream outputStream = response.getOutputStream();
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        word.write(outputStream);
    }

    //想单元格中写入图片
    private void setCellImage(XWPFTableCell cell, File imagesFile){
        XWPFRun run = cell.getParagraphs().get(0).createRun();
        try (FileInputStream fileInputStream = new FileInputStream(imagesFile)) {
            run.addPicture(fileInputStream, XWPFDocument.PICTURE_TYPE_JPEG, imagesFile.getName(), Units.toEMU(100), Units.toEMU(50));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 用于深克隆行
     *
     * @param table
     * @param sourceRow
     * @param rowIndex
     */
    private void copyRow(XWPFTable table, XWPFTableRow sourceRow, int rowIndex) {
        XWPFTableRow targetRow = table.insertNewTableRow(rowIndex);
        //行属性
        targetRow.getCtRow().setTrPr(sourceRow.getCtRow().getTrPr());

        //获取源行的单元格
        List<XWPFTableCell> cells = sourceRow.getTableCells();
        if (CollectionUtils.isEmpty(cells)) {
            return;
        }
        XWPFTableCell targetCell = null;
        for (XWPFTableCell cell : cells) {
            targetCell = targetRow.addNewTableCell();
            //附上单元格的样式
            //单元格的属性
            targetCell.getCTTc().setTcPr(cell.getCTTc().getTcPr());
            //单元格中段落属性
            targetCell.getParagraphs().get(0).getCTP().setPPr(cell.getParagraphs().get(0).getCTP().getPPr());
        }
    }

    //使用EasyPoi方式导出excel
    public void downloadWithEasyPoi(HttpServletResponse response) throws IOException {
        ExportParams exportParams = new ExportParams("员工信息列表","数据", ExcelType.XSSF);
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, User.class, userMapper.selectList(null));

//            导出的文件名称
        String filename = "用户数据的导出.xlsx";
//            设置文件的打开方式和mime类型
        ServletOutputStream outputStream = response.getOutputStream();
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        workbook.write(outputStream);
    }

    /**
     * easypoi 导入excel
     * @param file
     */
    public void uploadExcelWithEasyPOI(MultipartFile file) throws Exception {
        ImportParams importParams = new ImportParams();
        importParams.setNeedSave(false);
        importParams.setTitleRows(1);
        importParams.setHeadRows(1);

        List<User> userList = ExcelImportUtil.importExcel(file.getInputStream(), User.class, importParams);
        // DB
        for (User user : userList) {
            user.setId(null);
            System.out.println(user.toString());
        }
    }

    public void downloadUserInfoByEasyPOI(Long id, HttpServletResponse response) throws IOException {
        File templatePath = new File(rootPath.getAbsolutePath(), "/excel_template/userInfo3.xlsx");

        TemplateExportParams exportParams = new TemplateExportParams(templatePath.getPath(),true);
        User user = this.findById(id);
        Map<String, Object> map = EntityUtils.entityToMap(user);
        ImageEntity imageEntity = new ImageEntity();
        imageEntity.setUrl(user.getPhoto());
        imageEntity.setWidth(100);
        imageEntity.setHeight(50);
        imageEntity.setColspan(2);
        imageEntity.setRowspan(4);
        map.put("photo",imageEntity);

        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, map);
        String filename = "用户详细信息数据.xlsx";
        //设置文件的打开方式和mime类型
        ServletOutputStream outputStream = response.getOutputStream();
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        workbook.write(outputStream);
        outputStream.close();
    }

    /**
     * 使用EasyPOI 导出CSV
     * @param response
     */
    public void downLoadCSVWithEasyPOI(HttpServletResponse response) throws IOException {
        CsvExportParams csvExportParams = new CsvExportParams();
        csvExportParams.setExclusions(new String[]{"一寸照片"});
        String filename = "百万数据导出.csv";
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("text/csv");
        List<User> userList = userMapper.selectList(null);
        CsvExportUtil.exportCsv(csvExportParams,User.class,userList,response.getOutputStream());
    }

    /**
     * 使用easypoi 下载合同
     * @param id
     * @param response
     */
    public void downloadContractWithEasyPOI(Long id, HttpServletResponse response) throws Exception {
        // 获取模板文档  准备数据  模板文档结合数据
        File templatePath = new File(rootPath.getAbsolutePath(), "/word_template/contract_template2.docx");

        //准备数据
        User user = this.findById(id);
        Map<String, Object> params = new HashMap<>();
        params.put("userName", user.getUserName());
        params.put("hireDate", sdf.format(user.getHireDate()));
        params.put("address", user.getAddress());
        ImageEntity entityContent = new ImageEntity();
        entityContent.setUrl(rootPath.getPath()+user.getPhoto());
        entityContent.setHeight(50);
        entityContent.setWidth(100);
        params.put("myImage", entityContent);

        List<Map> resourceListMap = new ArrayList<>();
        Map<String,Object> map = new HashMap<>();
        for (com.panghl.pojo.Resource resource : user.getResourceList()) {
            map.put("name",resource.getName());
            map.put("price",resource.getPrice());
            map.put("needReturn",resource.getNeedReturn());
            //map.put("photo",resource.getPhoto()); //需要处理
            /**
             * easyPOI在导出word时，发现导出的表格中没有我们预想的图片，所以easyPOI不是万能的，如果有同样的需求还是得用POI来做！
             */
            ImageEntity entity = new ImageEntity();
            entity.setUrl(rootPath.getPath()+"\\static"+resource.getPhoto());
            entity.setHeight(50);
            entity.setWidth(100);
            map.put("photo",entity);
            resourceListMap.add(map);
        }
        params.put("maplist",resourceListMap);

        XWPFDocument word = WordExportUtil.exportWord07(templatePath.getPath(), params);
        String filename = "员工" + user.getUserName() + "合同信息.docx";
//            设置文件的打开方式和mime类型
        ServletOutputStream outputStream = response.getOutputStream();
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        word.write(outputStream);
    }


    @Autowired
    private HikariDataSource hikariDataSource;

    public void downLoadPDF(HttpServletResponse response) throws Exception {
        //获取模板文件
        File templateFile = new File(rootPath.getAbsolutePath(), "/pdf_template/userList_db.jasper");
        ServletOutputStream outputStream = response.getOutputStream();
        //准备数据库的链接
        Map params = new HashMap();
        JasperPrint jasperPrint = JasperFillManager.fillReport(new FileInputStream(templateFile), params, hikariDataSource.getConnection());
        JasperExportManager.exportReportToPdfStream(jasperPrint,outputStream);
        String filename = "用户列表数据.pdf";
//            设置文件的打开方式和mime类型
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/pdf");
    }

    public void downLoadPDF2(HttpServletResponse response)  throws Exception {
        //获取模板文件
        //File templateFile = new File(rootPath.getAbsolutePath(), "/pdf_template/userList.jasper");
        File templateFile = new File(rootPath.getAbsolutePath(), "/pdf_template/userList2.jasper");
        ServletOutputStream outputStream = response.getOutputStream();
        Map params = new HashMap();

        List<User> userList = userMapper.selectList(new LambdaQueryWrapper<User>().orderByDesc(User::getProvince));
        for (User user : userList) {
            user.setHireDateStr(sdf.format(user.getHireDate()));
        }

        JRBeanCollectionDataSource jrBeanCollectionDataSource = new JRBeanCollectionDataSource(userList);
        JasperPrint jasperPrint = JasperFillManager.fillReport(new FileInputStream(templateFile), params, jrBeanCollectionDataSource);
        JasperExportManager.exportReportToPdfStream(jasperPrint,outputStream);
        String filename = "用户列表数据.pdf";
//            设置文件的打开方式和mime类型
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/pdf");
    }

    public void downloadUserInfoByPDF(Long id, HttpServletResponse response)  throws Exception{
        User user = this.findById(id);
        //获取模板文件
        File templateFile = new File(rootPath.getAbsolutePath(), "/pdf_template/userInfo.jasper");
        ServletOutputStream outputStream = response.getOutputStream();
        Map<String, Object> params = EntityUtils.entityToMap(user);
        params.put("salary",params.get("salary").toString());
        params.put("birthday",sdf.format(user.getBirthday()));
        params.put("photo",rootPath.getPath()+user.getPhoto());
        JasperPrint jasperPrint = JasperFillManager.fillReport(new FileInputStream(templateFile), params, new JREmptyDataSource());
        JasperExportManager.exportReportToPdfStream(jasperPrint,outputStream);

        String filename = "用户详细信息.pdf";
//            设置文件的打开方式和mime类型
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/pdf");
    }

}
